Imports System.Data
Imports System.Data.SqlClient

Imports TaxonNamesInterface

Public Class ReferenceData
    Private Shared ConnectionString As String = Configuration.ConfigurationManager.ConnectionStrings("names").ConnectionString

#Region "References"

    Private Shared Function LoadReferenceFromDs(ByVal dbRef As Reference) As TaxonNamesInterface.Reference
        Dim ref As New TaxonNamesInterface.Reference

        ref.ChangeDate = dbRef.ChangeDate
        ref.ChangeUser = dbRef.ChangeUser
        ref.Citation = dbRef.Citation
        ref.FullCitation = dbRef.FullCitation
        ref.ReferenceId = dbRef.ReferenceId
        ref.ReferenceLSID = dbRef.ReferenceLSID

        Return ref
    End Function

    Public Shared Function GetReference(ByVal referenceId As Guid) As TaxonNamesInterface.Reference
        Dim ref As TaxonNamesInterface.Reference = Nothing
        Dim db As New ReferencesDB(ConnectionString)

        Dim ds = From tbl In db.References Where tbl.ReferenceId = referenceId Select tbl
        If ds.Count > 0 Then
            ref = LoadReferenceFromDs(ds.First)
        End If

        Return ref
    End Function

    Public Shared Function SearchReferences(ByVal searchTxt As String, ByVal maxResults As Integer) As List(Of TaxonNamesInterface.Reference)
        Dim refs As New List(Of TaxonNamesInterface.Reference)
        Dim db As New ReferencesDB(ConnectionString)

        Dim ds = From tbl In db.References _
                 Where tbl.Citation.Contains(searchTxt) _
                 Select tbl

        Dim cnt As Integer = 1
        For Each r In ds
            refs.Add(LoadReferenceFromDs(r))
            cnt += 1
            If maxResults > 0 AndAlso cnt > maxResults Then Exit For
        Next

        Return refs
    End Function


    Public Shared Sub InsertUpdateReference(ByVal newRef As TaxonNamesInterface.Reference, ByVal user As String)
        Dim db As New ReferencesDB

        Dim dbref As New Reference

        If newRef.ReferenceId = Guid.Empty Then
            dbref.ReferenceId = Guid.NewGuid
            db.References.InsertOnSubmit(dbref)
        Else
            dbref = (From tbl In db.References Where tbl.ReferenceId = newRef.ReferenceId Select tbl).First
        End If

        dbref.ReferenceId = newRef.ReferenceId
        dbref.ReferenceLSID = TaxonNamesInterface.Reference.CreateLSID(dbref.ReferenceId)
        dbref.FullCitation = newRef.FullCitation
        dbref.Citation = newRef.Citation
        dbref.ChangeUser = user
        dbref.ChangeDate = DateTime.Now

        db.SubmitChanges()

        newRef.ReferenceId = dbref.ReferenceId
        newRef.ReferenceLSID = dbref.ReferenceLSID
        newRef.ChangeUser = dbref.ChangeUser
        newRef.ChangeDate = dbref.ChangeDate

    End Sub


    Public Shared Sub DeleteReference(ByVal refLsid As String, ByVal newRefLsid As String, ByVal user As String)
        Dim db As New ReferencesDB(ConnectionString)

        Dim ref = (From tbl In db.References Select tbl Where tbl.ReferenceLSID = refLsid).First

        If ref IsNot Nothing Then
            Dim ndb As New NamesDB(ConnectionString)

            Dim dep As New Deprecated
            dep.Date = DateTime.Now
            dep.DeprecatedBy = user
            dep.DeprecatedLSID = refLsid
            dep.NewLSID = newRefLsid
            dep.DeprecatedTable = "Reference"

            ndb.Deprecateds.InsertOnSubmit(dep)

            db.References.DeleteOnSubmit(ref)

            db.SubmitChanges()
            ndb.SubmitChanges()

        End If
    End Sub

    Public Shared Function GetReferenceLSIDs(ByVal pageNumber As Integer, ByVal pageSize As Integer) As List(Of String)
        Dim lsids As New List(Of String)
        Dim db As New ReferencesDB(ConnectionString)
        Dim total As Integer = (pageNumber - 1) * pageSize

        Dim ls = From tbl In db.References.Take(total) Select tbl.ReferenceLSID

        For Each lsid As String In ls
            lsids.Add(lsid)
        Next

        Return lsids
    End Function


#End Region

#Region "ReferenceRIS"

    Private Shared Function LoadRISFromDs(ByVal dbRIS As ReferenceRI) As TaxonNamesInterface.ReferenceRIS
        Dim ris As New TaxonNamesInterface.ReferenceRIS
        ris.Authors = dbRIS.Authors
        ris.Authors2 = dbRIS.Authors2
        ris.Authors3 = dbRIS.Authors3
        ris.ChangeDate = dbRIS.ChangeDate
        ris.ChangeUser = dbRIS.ChangeUser
        ris.CityOfPublication = dbRIS.CityOfPublication
        ris.Date = dbRIS.Date
        ris.EndPage = dbRIS.EndPage
        ris.Identifier = dbRIS.Identifier
        ris.ISSNNumber = dbRIS.ISSNNumber
        ris.Issue = dbRIS.Issue
        ris.JournalName = dbRIS.JournalName
        ris.Keywords = dbRIS.Keywords
        ris.Notes = dbRIS.Notes
        ris.Publisher = dbRIS.Publisher
        ris.ReferenceId = dbRIS.ReferenceId
        ris.ReferenceRISId = dbRIS.RISId
        ris.StandardAbbreviation = dbRIS.StandardAbbreviation
        ris.StartPage = dbRIS.StandardAbbreviation
        ris.StartPage = dbRIS.StartPage
        ris.Title = dbRIS.Title
        ris.Title2 = dbRIS.Title2
        ris.Title3 = dbRIS.Title3
        ris.Type = dbRIS.Type
        ris.Volume = dbRIS.Volume
        ris.WebUrl = dbRIS.WebUrl

        Return ris
    End Function

    Public Shared Function GetReferenceRISByReference(ByVal refId As Guid) As TaxonNamesInterface.ReferenceRIS
        Dim ris As TaxonNamesInterface.ReferenceRIS = Nothing

        Dim db As New ReferencesDB(ConnectionString)

        Dim dbris = (From tbl In db.ReferenceRIs Select tbl Where tbl.ReferenceId = refId).First
        If dbris IsNot Nothing Then
            ris = LoadRISFromDs(dbris)
        End If

        Return ris
    End Function


#End Region


End Class
